Index

• B*Tree 인덱스는 나뭇잎으로 무성한 나무를 뒤집어 놓은 듯한 모습
• Root에서 Leaf 블럭까지의 거리를 깊이 (Height) 라고 부르며, 인덱스의 반복 탐색시 성능에 영향을 미치는 요소

• Root / Branch 블럭은 하위 노드들의 데이터 값 범위를 나타내는 Key 값과, 키 값에 해당하는 블록 주소 정보를 가지고 있음

• Leaf 블럭은 인덱스 키 값을 가지고, 그 키값에 해당하는 테이블 레코드를 찾아갈 때 필요한 주소 정보(row id)를 가짐

  • 같은 키 값일때 row id순으로 정렬
  • 인덱스 키(key) 값 순으로 정렬되어 있어 Range Scan이 가능하고 정방향/역방향 (ASC/DESC) 스캔이 가능한 양방향 연결 리스트 구조!

• Range 범위가 넓은 경우엔, Index를 타는 것보다 Full Scan이 빠른 경우도 있음!

• Random IO : 블락 1개에서 데이터 1개 접근
• Sequential IO : 블락 1개에서 데이터 여러개 접근

1.1 목표 ( 인덱스 => 정렬)

1. 수직적 Search 효율화
2. 수평적 탐색 선택도 최대화
3. 테이블 Random Access ( Random IO ) 최소화

• 수직적 Search : 수평적 탐색을 위한 시작 지점을 찾는 과정
• 수평적 탐색 : Leaf 블럭에 저장된 레코드끼리 연결된 순서에 따른 스캔

• 비용 : 테이블 Random IO > 수직적 Search > 수평적 탐색

1.2 인덱스의 구조

1. Root Node : 가장 상위 노드 / 하위으 Branch Node 수만큼 Row를 가지고 있음
2. Branch Node : Root와 Leaf의 연결 고리 / 자기 하위의 LEaf Node 수만큼 Row를 가지고 있음
3. Leaf Node : Key + RowID로 구성 / Key 순서대로 정렬되어 있고, 이전 이후 Leaf의 Chain

수직적 탐색

• Root - Branch - Leaf
• 읽고자 하는 시작점 검색
• Random IO

수평적 탐색

• Leaf Block의 시작점부터 종료점까지
• Sequential IO

1.3 인덱스의 기본 원리

• 인덱스 사용이 불가능하거나 범위 스캔(Range Scan)이 불가능한 경우

  1. 인덱스 컬럼의 가공(좌변 가공) : 좌변을 가공하지 않고 우변을 가공하거나 사용합니다

     SELECT * FROM 업체 WHERE substr(업체명, 1, 2) = '대한'; (X)

     SELECT * FROM 업체 WHERE 업체명 like '대한%'; (O)

     SELECT * FROM 사원 WHERE 월급여 x 12 = 50000000; (X)

     SELECT * FROM 사원 WHERE 월급여 = 50000000 / 12; (O)

     SELECT * FROM 주문 WHERE to_char(일시, 'yyyymmdd') = :dt (X)

     SELECT * FROM 주문 WHERE 일시 >= to_date(:dt, 'yyyymmdd') and 일시 < to_date(:dt, 'yyyymmdd')+1 (O)

     and so on..

  2. Null의 검색 : Null 검색은 인덱스를 탈 수 없습니다

     SELECT * FROM 고객 WHERE 고객번호 IS NULL (X)

  3. 묵시적 형변환 : 컬럼과 상수의 Data Type이 상시할 경우. 문자를 숫자 변환 후 비교! but A001 같이 변환 불가능할 경우 에러 발생

     SELECT * FROM 고객 WHERE 고객번호 = 100 (X)

1.4 다양한 인덱스 스캔 방식

Index Range Scan

• between, unique값이 아닌 경우 사용-!
• 인덱스를 타는 대부분의 스캔 방식 중 99%가 Range Scan
• 그러나 항상 빠른 속도를 보장하진 않는다 ( 범위가 넓으면 full scan이 나음 )
• 인덱스 스캔하는 범위를 얼마나 줄일 수 있는가?
• 테이블로 액세스하는 회수를 얼만큼 줄일 수 있는가?
• SQL 튜닝 핵심 원리

• 인덱스를 구성하는 선두 컬럼을 조건절에 사용

  인덱스 : 부서코드 + 이름

  SELECT * FROM 사원 WHERE 이름 = '홍길동' ( X ) => 부서코드가 정렬이 되어있지 않음..!

  인덱스 : 이름 + 부서코드

  SELECT * FROM 사원 WHERE 이름 = '홍길동' ( O ) => 이름이 정렬되어 있음!

• 진행 순서는 같은 길이라면 위 -> 아래 / 우측 -> 좌측으로 진행

Index Range Scan Descending

• 인덱스를 뒤에서부터 앞쪽으로 스캔
• 나머지는 Index Range Scan과 동일

Index Full Scan

• 적당한 인덱스가 없을 경우 Table Full Scan 수행
• 조회 조건의 인덱스가 있지만 선두 컬럼이 아닌 경우, 옵티마이저가 인덱스 활용시 이익이 있다고 판단할 경우 사용
• 많다 적다의 판단은 Trace를 떠보고 결정
• 최종 결과 값이 적을 경우는 Full Table Scan보다 Indexl Full Scan이 효율적
• 최종 결과 값이 많을 경우는 Full Table Scan이 효율적

Index Unique Scan

• 수직적 스캔만 발생 ( 수평적 탐색은 없음 )
• Unique 인덱스일 경우 사용
• = 조건일 경우만 사용! between조건이 들어가면 당연히 수평적 탐색을 수행합니다

Index Skip Scan

• 조회 조건이 인덱스 선두 컬럼이 아니며, 인덱스 선두 컬럼의 Distinct가 매우 낮을 때 사용 (=데이터 값의 개수가 적어서 정렬할 수 있음)
• 인덱스 선두 컬럼이 between, like, 부등호일 때도 사용 가능

Index Fast Full Scan

• 전체 Index를 Full Scan
• Multi-Block IO
• 파라미터의 db_file_multiblock_read_count 개수만큼 한번에 read!
• Index의 논리적 순서와 무관하게 물리적 순서대로 Read
• 속도가 빠름
• 결과는 인덱스 키 컬럼의 순서와 무관
• 디스크에 인접한 것들을 뭉뜩그려서 가져옴

Oracle DBMS 구조

• 오라클 DBMS는 database와 instance로 나뉩니다

1) database

1. datafiles
2. Control files : 데이터베이스 전체의 정보를 지니고 있는 Oracle server Instance 를 open 할 때 두번 째 단계인 mount 단계로 가기 위해서 필요한 파일(Instance open 순서: nomount -> mount -> open) 
3. Redo Log files : 데이터의 변경이 생길 때 마다 장애를 대비해 변경되기 전과 후의 내용들 기록해 두는 파일 (Redo log buffer 에서 내려쓰는 log 파일)

2) instance

1. background

(1) CKPT : 체크포인트(CKPT)는 LGWR 프로세스에 의해 활동하며 사용자가 COMMIT문을 실행할 때마다 오라클 서버가 관리하는 시스템 변경번호(SYSTEM CHANGE NUMBER) 및 데이터베이스의 상태정보를 컨트롤 파일과 데이터 파일에 저장하는 작업을 하게됩니다. 또한, CKPT 프로세스가 발생하면 연속적으로 DBWR 프로세스가 작업을 수행
(2) LGWR : 사용자가 실행한 SQL문을 커밋(Commit)하면 화면에 '커밋이 성공적이다'라는 메시지를 보여줍니다. 이때 커밋했던 모든 작업내용을 리두로그 파일에 백업 하게되는데 이러한 작업을 로그 기록기(LGWR)가 처리해 줍니다. 모든 작업내역을 리두로그 파일에 저장하는 이유는 갑작스런 시스템의 다운 또는 데이터베이스의 다운 시 처리하고 있던 모든 작업내용을 다시 복구하기 위함!
(3) DBWR : 데이터베이스 기록기(DBWR)는 사용자가 실행한 SQL문에 의해 데이터의 변경내역(입력, 수정, 삭제)을 테이블에 저장하는 작업을 수행합니다. 예를 들어, 사용자가 UPDATE문을 실행하고 커밋(Commit)문을 실행할 때 테이블에 데이터를 저장하는 작업을 데이터베이스 기록기 프로세스가 처리합니다. 데이터베이스 기록기(DBWR)와 로그 기록기(LGWR)는 데이터베이스를 시작하면 자동으로 생성되고 종료하면 없어지는 백그라운드 프로세스
(4) SMON : 시스템 모니터(SMON)는 백그라운드 프로세스와 데이터베이스 메모리 영역의 상태를 감시하며 데이터베이스가 다운된 후 다시 시작될 때 자동적인 복구작업을 수행
(5) PMON : 사용자들이 데이터베이스에 접속하면 한번의 접속 요구마다 사용자 프로세스가 하나씩 생성됩니다. 프로세스 모니터(PMON)는 이러한 사용자 프로세스들의 상태를 감시합니다. 만약 어떤 사용자 프로세스에 오류가 발생하거나(예를 들어, SQL*PLUS에서 SQL문을 실행하는 중에 윈도우를 닫게 된다면) 또는 사용자 프로세스가 비정상적으로 종료된 경우 모든 작업을 자동적으로 롤백(Rollback) 시켜줍니다.

2. SGA

(1) Shared Area : 다른 사용자들과 어떤 대상을 공유하기 위해 만들어진 곳이다. 여러개의 공간으로 나누어 진다.(Library Cache / Data Dictionary Cache / Server Result Cache / Reserved Pool) 
(2) Data Buffer Cache : 실제 데이터의 조회와 변경 등의 작업이 일어나는 공간으로 사용자가 조회하거나 변경하려는 모든 데이터는 이 곳에 있어야만 한다. 즉, 파일에 저장되어 있는 어떤 데이터를 조회하거나 변경하려면, 해당 데이터가 있는 블록을 복사해서 이 곳으로 가져와서 작업을 진행한다. 이렇게 하는 이유는 디스크에서 작업하는 속도와 메모리에서 작업하는 속도를 비교했을 때 메모리가 훨씬 빠르기 때문
(3) Redo Log Buffer : DDL이나 DML 이 실행될 경우 (즉, 데이터의 변경이 생길 경우) , 해당 변경 내용을 기록해 두는 역할(장애시 복구하기 위함)

3) 기타

1. PGA ( Program Global Area ): SGA 가 공유 메모리라면, PGA 는 각 process 들이 개별적으로 사용하는 메모리 공간이다. 즉, 모든 server process 나 background process 들은 전부 각각의 PGA 를 가지고 있다.
2. Server Process

Reference

• 참고 블로그